using System;
using System.Collections.Generic;
using System.Text;
using DTOLayer;
using System.Collections;
using System.Data.OleDb;
using System.Data;

namespace BUSLayer
{
    public class SQLNhieuHonHaiBangDAO
    {
        public static List<SoTietKiemDTO> LaySoTietKiem_tenLoaiTietKiem_MaSoTietKiem(long ma, string ten)
        {
            List<SoTietKiemDTO> list = new List<SoTietKiemDTO>();
            try
            {

                OleDbConnection cn;
                cn = Database.ConnectionData();
                //string sql = "SELECT * From LOAITIETKIEM Where TenLoaiTietKiem Like 'e%'";
                string sql = @"Select * From SOTIETKIEM stk Where MaLoaiTietKiem = (Select MaLoaiTietKiem From LOAITIETKIEM Where  TenLoaiTietKiem Like '" + ten + "%')";
                if (ma != 0)
                {
                    // sql += "MaLoaiTietKiem=" + ma.ToString();
                }
                // OleDbCommand cmd = new OleDbCommand(sql, cn);
                OleDbCommand cmd = new OleDbCommand(sql, cn);
                OleDbDataReader dr;
                dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    SoTietKiemDTO stk = new SoTietKiemDTO();
                    stk.MaSo = long.Parse(dr["MaSo"].ToString());
                    stk.MaLoaiTK = long.Parse(dr["MaLoaiTietKiem"].ToString());
                    stk.HoTenKhachHang = dr["HoTenKhachHang"].ToString();
                    stk.CMND = dr["CMND"].ToString();
                    stk.DiaChi = dr["DiaChi"].ToString();
                    stk.NgayMoSo = DateTime.Parse(dr["NgayMoSo"].ToString());
                    stk.SoTienGoi = double.Parse(dr["SoTienGoi"].ToString());
                    stk.SoLanDaoHan = int.Parse(dr["SoLanDaoHan"].ToString());
                    stk.TinhTrangSo = int.Parse(dr["TinhTrangSo"].ToString());
                    stk.ThoiHanRutToiThieu = long.Parse(dr["ThoiHanRutToiThieu"].ToString());
                    stk.LaiSuat = double.Parse(dr["LaiSuat"].ToString());
                    stk.NgayDaoHan = DateTime.Parse(dr["NgayDaoHan"].ToString());

                    list.Add(stk);
                }
                cn.Close();
                return list;



            }
            catch
            {
            }
            return list;

        }
        //bao cao doanh so ngay**********************************************************

        public static List<BaoCaoDoanhSoNgayDTO> TinhDoanhSoNgay(DateTime ngaybc)
        {
            List<BaoCaoDoanhSoNgayDTO> ls = new List<BaoCaoDoanhSoNgayDTO>();


            // OleDbConnection cn;
            List<PhieuGoiTienDTO> lispg = new List<PhieuGoiTienDTO>();
            lispg = PhieuGoiTienDAO.TinhTongThuTheoMaSoVaNgayThu(ngaybc);

            List<PhieuRutTienDTO> listpr = new List<PhieuRutTienDTO>();
            listpr = PhieuRutTienDAO.TinhTongRutTheoMaSoVaNgayThu(ngaybc);

            List<LoaiTietKiemDTO> ltk = new List<LoaiTietKiemDTO>();
            ltk = LoaiTietKiemDAO.SelectLoaiTietKiemALL();


            double tongthu = 0;
            double tongchi = 0;
            double chenhlech = 0;

            for (int i = 0; i < ltk.Count; i++)
            {
                List<SoTietKiemDTO> stk = new List<SoTietKiemDTO>();

                stk = SoTietKiemDAO.LaySoTietKiemTheoMaLoaiTietKiem(ltk[i].MaLoaiTK);

                for (int j1 = 0; j1 < listpr.Count; j1++)
                {
                    for (int j2 = 0; j2 < stk.Count; j2++)
                    {
                        if (listpr[j1].MaSo == stk[j2].MaSo)
                        {
                            tongchi += listpr[j1].SoTienRut;
                        }
                    }
                }


                for (int j1 = 0; j1 < lispg.Count; j1++)
                {
                    for (int j2 = 0; j2 < stk.Count; j2++)
                    {
                        if (lispg[j1].MaSo == stk[j2].MaSo)
                        {
                            tongthu += lispg[j1].SoTienGoi;
                        }
                    }
                }

                for (int t = 0; t < stk.Count; t++)
                {
                    if (stk[t].NgayMoSo == ngaybc)
                    {
                        tongthu += stk[t].SoTienGoi;
                    }
                }

                chenhlech = tongthu - tongchi;

                BaoCaoDoanhSoNgayDTO bc = new BaoCaoDoanhSoNgayDTO();

                bc.MaLoaiTietKiem = ltk[i].MaLoaiTK;
                bc.TongChi = tongchi;
                bc.TongThu = tongthu;
                bc.NgayLapBaoCao = ngaybc;
                if (chenhlech < 0)
                    chenhlech *= -1;
                bc.ChenhLech = chenhlech;

                ls.Add(bc);

                chenhlech = 0;
                tongthu = 0;
                tongchi = 0;

            }

            return ls;

        }
        public static bool KiemTraManNhuan(int nam)
        {
            if (nam % 400 == 0 || (nam % 4 == 0 && nam % 100 == 0))
                return true;
            return false;
        }
        public static List<BaoCaoThangDTO> BaoCaoDongMoSo(long mltk, int thang, int nam)
        {
            List<BaoCaoThangDTO> ls = new List<BaoCaoThangDTO>();
            int sodong = 0;
            int somo = 0;
            int chenhlech = 0;

            int[] sn ={ 0, 31, 28, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31 };
            if (KiemTraManNhuan(nam) == true)
            {
                sn[2] = 29;
            }

            for (int i = 1; i <= sn[thang]; i++)
            {
                sodong = (int)SoTietKiemDAO.SoDong(mltk, i, thang, nam);
                somo = (int)SoTietKiemDAO.SoMo(mltk, i, thang, nam);

                chenhlech = somo - sodong;

                BaoCaoThangDTO bc = new BaoCaoThangDTO();
                bc.SoDong = sodong;
                bc.SoMo = somo;
                bc.ChenhLech = chenhlech;
                bc.Ngay = i;
                ls.Add(bc);

                somo = 0;
                sodong = 0;
                chenhlech = 0;

            }


            return ls;
        }

        public static List<BaoCaoThangDTO> BaoCaoDongMoSoSuLyHam(long mltk, int thang, int nam)
        {
            List<BaoCaoThangDTO> ls = new List<BaoCaoThangDTO>();
            int sodong = 0;
            int somo = 0;
            int chenhlech = 0;


            int[] sn ={ 0, 31, 28, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31 };
            if (KiemTraManNhuan(nam) == true)
            {
                sn[2] = 29;
            }
            List<SoTietKiemDTO> stk = new List<SoTietKiemDTO>();
            stk = SoTietKiemDAO.SelectAllSoTietKiemList();

            for (int i = 1; i <= sn[thang]; i++)
            {


                for (int j = 0; j < stk.Count; j++)
                {
                    long ml = stk[j].MaLoaiTK;
                    int th = stk[j].NgayMoSo.Month;
                    int n = stk[j].NgayMoSo.Year;
                    int ng = stk[j].NgayMoSo.Day;
                    if (ml == mltk && ng == i && th == thang && n == nam)
                    {
                        if (stk[j].TinhTrangSo == 0)
                        {
                            sodong++;
                        }
                        else
                        {
                            somo++;
                        }
                    }
                }

                chenhlech = somo - sodong;
                if (chenhlech < 0)
                    chenhlech *= -1;
                BaoCaoThangDTO bc = new BaoCaoThangDTO();
                bc.SoDong = sodong;
                bc.SoMo = somo;
                bc.ChenhLech = chenhlech;
                bc.Ngay = i;
                ls.Add(bc);

                somo = 0;
                sodong = 0;
                chenhlech = 0;

            }
            return ls;
        }
        public static DataTable ChuyenListBaoCaoDSNgay_DataTable(List<BaoCaoDoanhSoNgayDTO> ls)
        {
            DataTable kq = new DataTable();

            DataColumn Col1 = new DataColumn("MaBaoCaoDoanhSoNgay", System.Type.GetType("System.Int64"));
            // DataColumn Col1= new DataColumn ("MaBaoCaoDoanhSoNgay",System .Type .GetType (System.DateTime)
            kq.Columns.Add(Col1);
            DataColumn Col2 = new DataColumn("MaLoaiTietKiem", System.Type.GetType("System.Int64"));
            kq.Columns.Add(Col2);
            DataColumn Col3 = new DataColumn("NgayLapBaoCao", System.Type.GetType("System.DateTime"));
            kq.Columns.Add(Col3);
            DataColumn Col4 = new DataColumn("TongThu", System.Type.GetType("System.Double"));
            kq.Columns.Add(Col4);
            DataColumn Col5 = new DataColumn("TongChi", System.Type.GetType("System.Double"));
            kq.Columns.Add(Col5);
            DataColumn Col6 = new DataColumn("ChenhLech", System.Type.GetType("System.Double"));
            kq.Columns.Add(Col6);

            for (int i = 0; i < ls.Count; i++)
            {
                DataRow row = kq.NewRow();
                row["MaBaoCaoDoanhSoNgay"] = ls[i].MaBaoCaoNgay;
                row["MaLoaiTietKiem"] = ls[i].MaLoaiTietKiem;
                row["NgayLapBaoCao"] = ls[i].NgayLapBaoCao;
                row["TongThu"] = ls[i].TongThu;
                row["TongChi"] = ls[i].TongChi;
                row["ChenhLech"] = ls[i].ChenhLech;

                kq.Rows.Add(row);
            }

            return kq;

        }

        public static DataTable ChuyenListBaoCaoMoDongSoThang_DataTable(List<BaoCaoThangDTO> ls)
        {
            DataTable kq = new DataTable();

            DataColumn Col1 = new DataColumn("MaChiTietBaoCaoThang", System.Type.GetType("System.Int64"));
            //DataColumn Col1= new DataColumn ("MaBaoCaoDoanhSoNgay",System .Type .GetType (System.Int32)
            kq.Columns.Add(Col1);
            DataColumn Col2 = new DataColumn("MaBaoCaoThang", System.Type.GetType("System.Int64"));
            kq.Columns.Add(Col2);
            DataColumn Col3 = new DataColumn("NgayLapBaoCaoThang", System.Type.GetType("System.Int32"));
            kq.Columns.Add(Col3);
            DataColumn Col4 = new DataColumn("SoMo", System.Type.GetType("System.Double"));
            kq.Columns.Add(Col4);
            DataColumn Col5 = new DataColumn("SoDong", System.Type.GetType("System.Double"));
            kq.Columns.Add(Col5);
            DataColumn Col6 = new DataColumn("ChenhLech", System.Type.GetType("System.Double"));
            kq.Columns.Add(Col6);

            for (int i = 0; i < ls.Count; i++)
            {
                DataRow row = kq.NewRow();
                row["MaChiTietBaoCaoThang"] = i;
                row["MaBaoCaoThang"] = ls[i].MaBaoCaoThang;
                row["NgayLapBaoCaoThang"] = ls[i].Ngay;
                row["SoMo"] = ls[i].SoMo;
                row["SoDong"] = ls[i].SoDong;
                row["ChenhLech"] = ls[i].ChenhLech;


                kq.Rows.Add(row);
            }

            return kq;

        }
        public static DataTable LaySoCoLoaiTietKiemKhongKyHan()//kyhan=0
        {
            DataTable dt = new DataTable();
            // try
            {

                OleDbConnection cn;
                cn = Database.ConnectionData();
                string sql = @"SELECT * From SOTIETKIEM WHERE MaLoaiTietKiem in  (Select MaLoaiTietKiem From LOAITIETKIEM where KyHan=0)";
               
                // OleDbCommand cmd = new OleDbCommand(sql, cn);
                OleDbDataAdapter da;
                da = new OleDbDataAdapter(sql, cn);

                da.Fill(dt);
                cn.Close();
            }
            return dt;

        }
        public static double LayKyHanThemMaSo(long maso)
        {


            double kq;
            //B1&B2 tao chuoi kiet noi
            OleDbConnection cn;
            cn = Database.ConnectionData();
            //B3 Tao chuoi ket noi CSDL

            string sql = @"SELECT  LOAITIETKIEM.KyHan
                    FROM LOAITIETKIEM INNER JOIN SOTIETKIEM ON LOAITIETKIEM.MaLoaiTietKiem = SOTIETKIEM.MaLoaiTietKiem
                           WHERE SOTIETKIEM.MaSo = " + maso.ToString();
            //B4 thuc thi chuoi sql
            OleDbCommand cmd = new OleDbCommand(sql, cn);
            
            kq = double.Parse(cmd.ExecuteScalar().ToString());
            cn.Close();
            return kq;
        }
    }
}
